﻿using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Wms.IRepositories;
using Estsh.Core.Model.Result;
using Estsh.Core.Repositories;
using System.Collections;
using System.Data;
using System.Text;
using System.Security.Cryptography;
using System.Text.Json;
using Estsh.Core.Models;
using Newtonsoft.Json.Linq;
using Estsh.Core.Model.EnumUtil;
using Microsoft.AspNetCore.Mvc.Rendering;
using Estsh.Core.Model.Extend.IFModels;
using Estsh.Core.Model.Extend.Models;

namespace Estsh.Core.Wms.Repositories
{
    public class PullingRepository : BaseRepository<WmsPullingGroup>, IPullingRepository
    {
        public PullingRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }
        /// <summary>
        /// 查询JIT生产队列接口数据
        /// </summary>
        /// <returns></returns>
        public List<MesWmsWorkOrder> GetProductionQueueInfo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder sql = new StringBuilder(1024);
                sql.Append("SELECT top 3000 * FROM MES_WMS_WORK_ORDER(nolock) ");
                sql.Append("WHERE  syn_flg = 10 "); //未同步：10  已同步：20
                sql.Append("ORDER BY create_time  ");

                return dbConn.Query<MesWmsWorkOrder>(sql.ToString()).ToList();
            }
        }

		/// <summary>
		/// 查询JIT生产队列接口数据-亿日
		/// </summary>
		/// <returns></returns>
		public List<MesWmsWorkOrder_Extend> GetProductionQueueExtendInfo()
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				StringBuilder sql = new StringBuilder(1024);
				sql.Append("SELECT top 3000 * FROM MES_WMS_WORK_ORDER(nolock) ");
				sql.Append("WHERE  syn_flg = 10 "); //未同步：10  已同步：20
				sql.Append("ORDER BY create_time  ");

				return dbConn.Query<MesWmsWorkOrder_Extend>(sql.ToString()).ToList();
			}
		}


		/// <summary>
		/// 处理JIT生产队列接口数据
		/// </summary>
		/// <param name="mesWmsWorks"></param>
		/// <returns></returns>
		public bool InsertProductionQueueInfo(List<MesWmsWorkOrder> mesWmsWorks)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append(" SELECT * from sys_part(nolock) ");//零件号
                List<SysPart> sysPartsAll = dbConn.Query<SysPart>(stringBuilder.ToString()).ToList();

                stringBuilder = new StringBuilder();
                stringBuilder.Append(" SELECT * from sys_pdline(nolock) ");//产线
                List<SysPdline> sysPdlinesAll = dbConn.Query<SysPdline>(stringBuilder.ToString()).ToList();

                StringBuilder factoryStringBuilder = new StringBuilder(1024);
                factoryStringBuilder.Append("select * from sys_factory(nolock) ");//工厂代码获取工厂ID
                List<SysFactory> sysFactoryAll = dbConn.Query<SysFactory>(factoryStringBuilder.ToString()).ToList();

                MesWmsWorkOrder mesWmsWork = new MesWmsWorkOrder();
                for (int i = 0; i < mesWmsWorks.Count; i++)
                {
                    List<string> sqlList = new List<string>();
                    List<DynamicParameters> parametersList = new List<DynamicParameters>();
                    mesWmsWork = mesWmsWorks[i];

                    StringBuilder SqlStringBuilder = new StringBuilder(1024);
                    DynamicParameters parameters = new DynamicParameters();

                    List<SysPart> sysParts = sysPartsAll.Where(a => a.PartNo == mesWmsWork.PartNo).ToList();//零件号ID

                    List<SysPdline> sysPdlines = sysPdlinesAll.Where(a => a.PdlineCode == mesWmsWork.PdlineCode).ToList();//产线

                    List<SysFactory> sysFactory = sysFactoryAll.Where(a => a.FactoryCode == mesWmsWork.FactoryCode).ToList();//工厂

                    StringBuilder customerStringBuilder = new StringBuilder(1024);
                    customerStringBuilder.Append("select * from sys_customer(nolock) where customer_code='" + mesWmsWork.CustomerCode + "'");//客户信息
                    List<SysCustomer> sysCustomers = dbConn.Query<SysCustomer>(customerStringBuilder.ToString()).ToList();

                    SqlStringBuilder = new StringBuilder(1024);
                    SqlStringBuilder.Append("select * from sys_bom_detail(nolock) where part_no='" + mesWmsWork.PartNo + "' ");//BOM明细表
					List<SysBomDetail> sysBomDetails = dbConn.Query<SysBomDetail>(SqlStringBuilder.ToString()).ToList();
                    foreach (SysBomDetail sysBom in sysBomDetails)
                    {
                        //明细
                        stringBuilder = new StringBuilder();
                        stringBuilder.Append(" SELECT * from mes_work_order_part(nolock) WHERE order_no='" + mesWmsWork.WorkOrderNo + "' and parent_part_no='" + mesWmsWork.PartNo + "' and part_no='" + sysBom.ItemPartNo + "' ");
                        List<MesWorkOrderPart> mesWorkOrderParts = dbConn.Query<MesWorkOrderPart>(stringBuilder.ToString()).ToList();
                        if (mesWorkOrderParts.Count > 0)
                        {
                            SqlStringBuilder = new StringBuilder(1024);
                            SqlStringBuilder.Append("UPDATE dbo.mes_work_order_part ");
                            SqlStringBuilder.Append("   SET order_no = @orderNo,part_id = @partId ");
                            SqlStringBuilder.Append("      ,part_no = @partNo,part_spec = @partSpec ");
                            SqlStringBuilder.Append("      ,qty = @qty,unit = @unit ");
                            SqlStringBuilder.Append("      ,pull_status = @pullStatus,parent_part_id = @parentPartId ");
                            SqlStringBuilder.Append("      ,parent_part_no = @parentPartNo,parent_part_spec = @parentPartSpec ");
                            SqlStringBuilder.Append("      ,factory_id = @factoryId ");
                            SqlStringBuilder.Append("      ,factory_code = @factoryCode,enabled = @enabled ");
                            SqlStringBuilder.Append("      ,update_userid = @updateUserid,update_time = @updateTime ");
                            SqlStringBuilder.Append(" WHERE GUID = @GUID ");

                            sqlList.Add(SqlStringBuilder.ToString());
                            parameters = new DynamicParameters();
                            parameters.Add("@orderNo", mesWmsWork.WorkOrderNo);//JIT单号


                            stringBuilder = new StringBuilder();
                            stringBuilder.Append(" SELECT * from sys_part(nolock) WHERE part_no='" + sysBom.ItemPartNo + "'");
                            List<SysPart> itemSysParts = dbConn.Query<SysPart>(stringBuilder.ToString()).ToList();

                            if (itemSysParts.Count > 0)
                            {
                                parameters.Add("@partId", itemSysParts[0].PartId);//子零件号ID
                                parameters.Add("@partSpec", itemSysParts[0].PartSpec);//子简码
                                parameters.Add("@unit", itemSysParts[0].Unit);//子单位
                            }
                            else
                            {
                                parameters.Add("@partId", 0);//子零件号ID
                                parameters.Add("@partSpec", "");//子简码
                                parameters.Add("@unit", "");//子单位
                            }
                            parameters.Add("@partNo", sysBom.ItemPartNo);//子零件号
                            parameters.Add("@qty", mesWmsWork.Qty * sysBom.Qty);//成品个数*零件号数量
                            parameters.Add("@pullStatus", "10");//状态

                            if (sysParts.Count > 0)
                            {
                                parameters.Add("@parentPartId", sysParts[0].PartId);//父零件号ID
                                parameters.Add("@parentPartNo", sysParts[0].PartNo);//父零件号
                                parameters.Add("@parentPartSpec", sysParts[0].PartSpec);//父简码
                            }
                            else
                            {
                                parameters.Add("@parentPartId", "0");//父零件号ID
                                parameters.Add("@parentPartNo", "");//父零件号
                                parameters.Add("@parentPartSpec", "");//父简码
                            }


                            if (sysFactory.Count == 0)
                            {
                                parameters.Add("@factoryId", 0);//工厂ID
                            }
                            else
                            {
                                parameters.Add("@factoryId", sysFactory[0].FactoryId);//工厂ID
                            }
                            parameters.Add("@factoryCode", mesWmsWork.FactoryCode);//工厂代码
                            parameters.Add("@enabled", 'Y');//启用
                            parameters.Add("@updateUserid", 0);//修改人
                            parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//修改日期
                            parameters.Add("@GUID", mesWorkOrderParts[0].Guid);//GUID
                            parametersList.Add(parameters);
                        }
                        else
                        {
                            SqlStringBuilder = new StringBuilder(1024);
                            SqlStringBuilder.Append("INSERT INTO dbo.mes_work_order_part(order_no,part_id,part_no,part_spec,qty,unit,pull_status ");
                            SqlStringBuilder.Append("           ,parent_part_id,parent_part_no,parent_part_spec,factory_id,factory_code,enabled ");
                            SqlStringBuilder.Append("           ,create_userid,create_time) ");
                            SqlStringBuilder.Append("     VALUES(@orderNo,@partId,@partNo,@partSpec,@qty,@unit,@pullStatus,@parentPartId,@parentPartNo, ");
                            SqlStringBuilder.Append("	       @parentPartSpec,@factoryId,@factoryCode,@enabled,@createUserid,@createTime) ");

                            sqlList.Add(SqlStringBuilder.ToString());
                            parameters = new DynamicParameters();
                            parameters.Add("@orderNo", mesWmsWork.WorkOrderNo);//JIT单号

                            stringBuilder = new StringBuilder();
                            stringBuilder.Append(" SELECT * from sys_part(nolock) WHERE part_no='" + sysBom.ItemPartNo + "'");
                            List<SysPart> itemSysParts = dbConn.Query<SysPart>(stringBuilder.ToString()).ToList();

                            if (itemSysParts.Count > 0)
                            {
                                parameters.Add("@partId", itemSysParts[0].PartId);//子零件号ID
                                parameters.Add("@partSpec", itemSysParts[0].PartSpec);//子简码
                                parameters.Add("@unit", itemSysParts[0].Unit);//子单位
                            }
                            else
                            {
                                parameters.Add("@partId", 0);//子零件号ID
                                parameters.Add("@partSpec", "");//子简码
                                parameters.Add("@unit", "");//子单位
                            }

                            parameters.Add("@partNo", sysBom.ItemPartNo);//子零件号
                            parameters.Add("@qty", mesWmsWork.Qty * sysBom.Qty);//成品个数*零件号数量
                            parameters.Add("@pullStatus", "10");//状态

                            if (sysParts.Count > 0)
                            {
                                parameters.Add("@parentPartId", sysParts[0].PartId);//父零件号ID
                                parameters.Add("@parentPartNo", sysParts[0].PartNo);//父零件号
                                parameters.Add("@parentPartSpec", sysParts[0].PartSpec);//父简码
                            }
                            else
                            {
                                parameters.Add("@parentPartId", "0");//父零件号ID
                                parameters.Add("@parentPartNo", "");//父零件号
                                parameters.Add("@parentPartSpec", "");//父简码
                            }

                            if (sysFactory.Count == 0)
                            {
                                parameters.Add("@factoryId", 0);//工厂ID
                            }
                            else
                            {
                                parameters.Add("@factoryId", sysFactory[0].FactoryId);//工厂ID
                            }
                            parameters.Add("@factoryCode", mesWmsWork.FactoryCode);//工厂代码
                            parameters.Add("@enabled", 'Y');//启用
                            parameters.Add("@createUserid", 0);//创建人
                            parameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//创建日期
                            parametersList.Add(parameters);
                        }
                    }


                    //主表
                    stringBuilder = new StringBuilder();
                    stringBuilder.Append(" SELECT * from mes_work_order(nolock) WHERE order_no='" + mesWmsWork.WorkOrderNo + "' and part_no='" + mesWmsWork.PartNo + "'");
                    List<MesWorkOrder> mesWorkOrders = dbConn.Query<MesWorkOrder>(stringBuilder.ToString()).ToList();
                    if (mesWorkOrders.Count > 0)
                    {
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("UPDATE dbo.mes_work_order ");
                        SqlStringBuilder.Append("   SET order_type = @orderType,order_status = @orderStatus ");
                        SqlStringBuilder.Append("      ,order_seq = @orderSeq,part_id = @partId ");
                        SqlStringBuilder.Append("      ,part_no = @partNo,part_spec = @partSpec ");
                        SqlStringBuilder.Append("      ,qty = @qty,complete_qty = @completeQty ");
                        SqlStringBuilder.Append("      ,order_source = @orderSource,pdline_id = @pdlineId ");
                        SqlStringBuilder.Append("      ,pdline_code = @pdlineCode ");
                        SqlStringBuilder.Append("      ,customer_id = @customerId ");
                        SqlStringBuilder.Append("      ,customer_code = @customerCode,cust_order_no = @custOrderNo ");
                        SqlStringBuilder.Append("      ,vin_code = @vinCode ");
                        SqlStringBuilder.Append("      ,vehicle_no = @vehicleNo,vehicle_name = @vehicleName ");
                        SqlStringBuilder.Append("      ,factory_id = @factoryId,factory_code = @factoryCode ");
                        SqlStringBuilder.Append("      ,enabled = @enabled,update_userid = @updateUserid ");
                        SqlStringBuilder.Append("      ,update_time = @updateTime ");
                        SqlStringBuilder.Append(" WHERE guid = @guid ");

                        sqlList.Add(SqlStringBuilder.ToString());
                        parameters = new DynamicParameters();
                        parameters.Add("@orderType", mesWmsWork.OrderType);//JIT类型
                        parameters.Add("@orderStatus", "10");//状态
                        parameters.Add("@orderSeq", mesWmsWork.OrderSeq);//JIT序号

                        if (sysParts.Count > 0)
                        {
                            parameters.Add("@partId", sysParts[0].PartId);//零件号ID
                            parameters.Add("@partNo", sysParts[0].PartNo);//零件号
                            parameters.Add("@partSpec", sysParts[0].PartSpec);//简码
                        }
                        else
                        {
                            parameters.Add("@partId", 0);//零件号ID
                            parameters.Add("@partNo", mesWmsWork.PartNo);//零件号
                            parameters.Add("@partSpec", "");//简码
                        }
                        parameters.Add("@qty", mesWmsWork.Qty);//数量
                        parameters.Add("@completeQty", 0);//数量
                        parameters.Add("@orderSource", "mes");//单据来源
                        if (sysPdlines.Count > 0)
                        {
                            parameters.Add("@pdlineId", sysPdlines[0].PdlineId);//生产线id
                            parameters.Add("@pdlineCode", sysPdlines[0].PdlineCode);//生产线代码
                        }
                        else
                        {
                            parameters.Add("@pdlineId", 0);//生产线id
                            parameters.Add("@pdlineCode", mesWmsWork.PdlineCode);//生产线代码
                        }

                        if (sysCustomers.Count == 0)
                        {
                            parameters.Add("@customerId", 0);//客户ID
                        }
                        else
                        {
                            parameters.Add("@customerId", sysCustomers[0].CustomerId);//客户ID
                        }
                        parameters.Add("@customerCode", mesWmsWork.CustomerCode);//客户代码
                        parameters.Add("@custOrderNo", mesWmsWork.CustOrderNo);//客户代码
                        parameters.Add("@vinCode", mesWmsWork.VinCode);//Vin代码
                        parameters.Add("@vehicleNo", mesWmsWork.VehicleNo);//配置代码
                        parameters.Add("@vehicleName", mesWmsWork.VehicleName);//配置描述

                        if (sysFactory.Count == 0)
                        {
                            parameters.Add("@factoryId", 0);//工厂ID
                        }
                        else
                        {
                            parameters.Add("@factoryId", sysFactory[0].FactoryId);//工厂ID
                        }
                        parameters.Add("@factoryCode", mesWmsWork.FactoryCode);//工厂代码
                        parameters.Add("@enabled", 'Y');//启用
                        parameters.Add("@updateUserid", 0);//修改人
                        parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//修改日期
                        parameters.Add("@GUID", mesWorkOrders[0].Guid);//GUID
                        parametersList.Add(parameters);
                    }
                    else
                    {
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("INSERT INTO dbo.mes_work_order(order_no,order_type,order_status,order_seq,part_id,part_no,part_spec ");
                        SqlStringBuilder.Append("           ,qty,complete_qty,order_source,pdline_id,pdline_code ");
                        SqlStringBuilder.Append("           ,customer_id,customer_code,cust_order_no,cust_part_no,vin_code,vehicle_no,vehicle_name,pull_status ");
                        SqlStringBuilder.Append("           ,factory_id,factory_code,enabled,create_userid,create_time) ");
                        SqlStringBuilder.Append("     VALUES(@orderNo,@orderType,@orderStatus,@orderSeq,@partId,@partNo,@partSpec ");
                        SqlStringBuilder.Append("           ,@qty,@completeQty,@orderSource,@pdlineId,@pdlineCode,@customerId,@customerCode ");
                        SqlStringBuilder.Append("           ,@custOrderNo,@custPartNo,@vinCode,@vehicleNo,@vehicleName,@pullStatus,@factoryId,@factoryCode ");
                        SqlStringBuilder.Append("           ,@enabled,@createUserid,@createTime) ");

                        sqlList.Add(SqlStringBuilder.ToString());
                        parameters = new DynamicParameters();
                        parameters.Add("@orderNo", mesWmsWork.WorkOrderNo);//JIT单号
                        parameters.Add("@orderType", mesWmsWork.OrderType);//JIT类型
                        parameters.Add("@orderStatus", "10");//状态
                        parameters.Add("@orderSeq", mesWmsWork.OrderSeq);//JIT序号

                        if (sysParts.Count > 0)
                        {
                            parameters.Add("@partId", sysParts[0].PartId);//零件号ID
                            parameters.Add("@partSpec", sysParts[0].PartSpec);//简码
                        }
                        else
                        {
                            parameters.Add("@partId", 0);//零件号ID
                            parameters.Add("@partSpec", "");//简码
                        }
                        parameters.Add("@partNo", mesWmsWork.PartNo);//零件号

                        parameters.Add("@qty", mesWmsWork.Qty);//数量
                        parameters.Add("@completeQty", 0);//数量
                        parameters.Add("@orderSource", "mes");//单据来源
                        if (sysPdlines.Count > 0)
                        {
                            parameters.Add("@pdlineId", sysPdlines[0].PdlineId);//生产线id
                            parameters.Add("@pdlineCode", sysPdlines[0].PdlineCode);//生产线代码
                        }
                        else
                        {
                            parameters.Add("@pdlineId", 0);//生产线id
                            parameters.Add("@pdlineCode", mesWmsWork.PdlineCode);//生产线代码
                        }

                        if (sysCustomers.Count == 0)
                        {
                            parameters.Add("@customerId", 0);//客户ID
                        }
                        else
                        {
                            parameters.Add("@customerId", sysCustomers[0].CustomerId);//客户ID
                        }
                        parameters.Add("@customerCode", mesWmsWork.CustomerCode);//客户代码
                        parameters.Add("@custOrderNo", mesWmsWork.CustOrderNo);//客户代码
                        parameters.Add("@custPartNo", "");//客户代码

                        parameters.Add("@vinCode", mesWmsWork.VinCode);//Vin代码
                        parameters.Add("@vehicleNo", mesWmsWork.VehicleNo);//配置代码
                        parameters.Add("@vehicleName", mesWmsWork.VehicleName);//配置描述
                        parameters.Add("@pullStatus", 10);//拉动状态

                        if (sysFactory.Count == 0)
                        {
                            parameters.Add("@factoryId", 0);//工厂ID
                        }
                        else
                        {
                            parameters.Add("@factoryId", sysFactory[0].FactoryId);//工厂ID
                        }
                        parameters.Add("@factoryCode", mesWmsWork.FactoryCode);//工厂代码
                        parameters.Add("@enabled", 'Y');//启用
                        parameters.Add("@createUserid", 0);//创建人
                        parameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//创建日期
                        parametersList.Add(parameters);
                    }

                    SqlStringBuilder = new StringBuilder(1024);
                    SqlStringBuilder.Append("update MES_WMS_WORK_ORDER set syn_flg=@synFlg,syn_time=@synTime,update_userid=@updateUserid,update_time=@updateTime where GUID=@GUID ");//更新同步状态
                    sqlList.Add(SqlStringBuilder.ToString());

                    parameters = new DynamicParameters();
                    parameters.Add("@GUID", mesWmsWork.Guid);//ID
                    parameters.Add("@synFlg", "20");//已同步
                    parameters.Add("@synTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//同步时间
                    parameters.Add("@updateUserid", 0);//创建人
                    parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//创建时间
                    parametersList.Add(parameters);

                    bool staResult = ExecuteTransaction(sqlList, parametersList);
                    if (staResult == false)
                    {
                        sqlList = new List<string>();
                        parametersList = new List<DynamicParameters>();
                        SqlStringBuilder = new StringBuilder(1024);
                        SqlStringBuilder.Append("update MES_WMS_WORK_ORDER set syn_flg=@synFlg,syn_time=@synTime,err_msg=@errMsg,update_userid=@updateUserid,update_time=@updateTime where GUID=@GUID ");//更新同步状态
                        sqlList.Add(SqlStringBuilder.ToString());

                        parameters = new DynamicParameters();
                        parameters.Add("@GUID", mesWmsWork.Guid);//ID
                        parameters.Add("@synFlg", "30");//同步异常
                        parameters.Add("@synTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//同步时间
                        parameters.Add("@errMsg", "同步错误，请检查接口数据！");//错误
                        parameters.Add("@updateUserid", 0);//更新人
                        parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//更新时间
                        parametersList.Add(parameters);
                        ExecuteTransaction(sqlList, parametersList);
                        return false;
                    }
                }
                return true;
            }
        }


		/// <summary>
		/// 处理JIT生产队列接口数据-亿日
		/// </summary>
		/// <param name="mesWmsWorks"></param>
		/// <returns></returns>
		public bool InsertProductionQueueExtendInfo(List<MesWmsWorkOrder_Extend> mesWmsWorks)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				StringBuilder stringBuilder = new StringBuilder();
				stringBuilder.Append(" SELECT * from sys_part(nolock) ");//零件号
				List<SysPart> sysPartsAll = dbConn.Query<SysPart>(stringBuilder.ToString()).ToList();

                stringBuilder = new StringBuilder();
                stringBuilder.Append(" SELECT * from sys_pdline(nolock) ");//产线
                List<SysPdline> sysPdlinesAll = dbConn.Query<SysPdline>(stringBuilder.ToString()).ToList();

                StringBuilder factoryStringBuilder = new StringBuilder(1024);
                factoryStringBuilder.Append("select * from sys_factory(nolock) ");//工厂代码获取工厂ID
                List<SysFactory> sysFactoryAll = dbConn.Query<SysFactory>(factoryStringBuilder.ToString()).ToList();

                MesWmsWorkOrder_Extend mesWmsWork = new MesWmsWorkOrder_Extend();
				for (int i = 0; i < mesWmsWorks.Count; i++)
				{
					List<string> sqlList = new List<string>();
					List<DynamicParameters> parametersList = new List<DynamicParameters>();
					mesWmsWork = mesWmsWorks[i];

					StringBuilder SqlStringBuilder = new StringBuilder(1024);
					DynamicParameters parameters = new DynamicParameters();

					List<SysPart> sysParts = sysPartsAll.Where(a => a.PartNo == mesWmsWork.PartNo).ToList();//零件号ID

                    List<SysPdline> sysPdlines = sysPdlinesAll.Where(a => a.PdlineCode == mesWmsWork.PdlineCode).ToList();//产线

                    List<SysFactory> sysFactory = sysFactoryAll.Where(a => a.FactoryCode == mesWmsWork.FactoryCode).ToList();//工厂

                    //StringBuilder customerStringBuilder = new StringBuilder(1024);
                    //customerStringBuilder.Append("select * from sys_customer(nolock) where customer_code='" + mesWmsWork.CustomerCode + "'");//客户信息
                    //List<SysCustomer> sysCustomers = dbConn.Query<SysCustomer>(customerStringBuilder.ToString()).ToList();

                    SqlStringBuilder = new StringBuilder(1024);
					


					//主表
					stringBuilder = new StringBuilder();
					stringBuilder.Append(" SELECT * from mes_work_order(nolock) WHERE order_no='" + mesWmsWork.WorkOrderNo + "' and part_no='" + mesWmsWork.PartNo + "'");
					List<MesWorkOrder> mesWorkOrders = dbConn.Query<MesWorkOrder>(stringBuilder.ToString()).ToList();
					if (mesWorkOrders.Count > 0)
					{
						SqlStringBuilder = new StringBuilder(1024);
						SqlStringBuilder.Append("UPDATE dbo.mes_work_order ");
						SqlStringBuilder.Append("   SET order_type = @orderType,order_status = @orderStatus ");
						SqlStringBuilder.Append("      ,order_seq = @orderSeq,part_id = @partId ");
						SqlStringBuilder.Append("      ,part_no = @partNo,part_spec = @partSpec ");
						SqlStringBuilder.Append("      ,qty = @qty,complete_qty = @completeQty ");
						SqlStringBuilder.Append("      ,order_source = @orderSource,pdline_id = @pdlineId ");
						SqlStringBuilder.Append("      ,pdline_code = @pdlineCode ");
                        //SqlStringBuilder.Append("      ,customer_id = @customerId ");

                        SqlStringBuilder.Append("      ,customer_code = @customerCode,cust_order_no = @custOrderNo ");
						SqlStringBuilder.Append("      ,vin_code = @vinCode ");
						SqlStringBuilder.Append("      ,vehicle_no = @vehicleNo,vehicle_name = @vehicleName ");
						SqlStringBuilder.Append("      ,factory_code = @factoryCode ");
						SqlStringBuilder.Append("      ,enabled = @enabled,update_userid = @updateUserid ");
						SqlStringBuilder.Append("      ,update_time = @updateTime ");

						SqlStringBuilder.Append("      ,ref_work_order_no = @ref_work_order_no,receiving_date=@receiving_date,order_delivery_date=@order_delivery_date");
						SqlStringBuilder.Append("      ,operation_code = @operation_code,operation_name=@operation_name,work_type=@work_type,quota_salary=@quota_salary,mould=@mould,frock=@frock,top_operation_code=@top_operation_code ");
						SqlStringBuilder.Append("      ,top_operation_name = @top_operation_name,post_operation_code=@post_operation_code,post_operation_name=@post_operation_name,pdline_name=@pdline_name ");
						SqlStringBuilder.Append("      ,part_name = @part_name,classes_code=@classes_code,classes_name=@classes_name, latest_end_time=@latest_end_time,produce_start_time=@produce_start_time,produce_end_time=@produce_end_time");
						SqlStringBuilder.Append("      ,produce_time = @produce_time,work_code=@work_code,change_time=@change_time, is_repair=@is_repair,equipment_code=@equipment_code");



						SqlStringBuilder.Append(" WHERE guid = @guid ");

						sqlList.Add(SqlStringBuilder.ToString());
						parameters = new DynamicParameters();
						parameters.Add("@orderType", mesWmsWork.OrderType);//JIT类型
						parameters.Add("@orderStatus", "10");//状态
						parameters.Add("@orderSeq", null);//JIT序号

						if (sysParts.Count > 0)
						{
							parameters.Add("@partId", sysParts[0].PartId);//零件号ID
							parameters.Add("@partNo", sysParts[0].PartNo);//零件号
							parameters.Add("@partSpec", sysParts[0].PartSpec);//简码
							parameters.Add("@part_name", sysParts[0].PartSpec2);//物料名称
						}
						else
						{
							parameters.Add("@partId", 0);//零件号ID
							parameters.Add("@partNo", mesWmsWork.PartNo);//零件号
							parameters.Add("@partSpec", "");//简码
							parameters.Add("@part_name", mesWmsWork.PartName);//物料名称
						}
						parameters.Add("@qty", mesWmsWork.Qty);////工作数量
						parameters.Add("@completeQty", 0);//数量
						parameters.Add("@orderSource", "mes");//单据来源
                        if (sysPdlines.Count > 0)////资源编码：工单所在工作中心的编码。
                        {
                            parameters.Add("@pdlineId", sysPdlines[0].PdlineId);//生产线id
                            parameters.Add("@pdlineCode", sysPdlines[0].PdlineCode);//生产线代码
                            parameters.Add("@pdline_name", sysPdlines[0].PdlineName);//生产线名称

                        }
                        else
                        {
                            parameters.Add("@pdlineId", 0);//生产线id
                            parameters.Add("@pdlineCode", mesWmsWork.PdlineCode);//生产线代码
                            parameters.Add("@pdline_name", mesWmsWork.PdlineName);//生产线名称
                        }

                        //if (sysCustomers.Count == 0)
                        //{
                        //	parameters.Add("@customerId", 0);//客户ID
                        //}
                        //else
                        //{
                        //	parameters.Add("@customerId", sysCustomers[0].CustomerId);//客户ID
                        //}
                        parameters.Add("@customerCode", null);//客户代码
						parameters.Add("@custOrderNo", null);//客户代码
						parameters.Add("@vinCode",null);//Vin代码
						parameters.Add("@vehicleNo", null);//配置代码
						parameters.Add("@vehicleName",null);//配置描述

                        //if (sysFactory.Count == 0)
                        //{
                        //    parameters.Add("@factoryId", 0);//工厂ID
                        //}
                        //else
                        //{
                        //    parameters.Add("@factoryId", sysFactory[0].FactoryId);//工厂ID
                        //}
                        parameters.Add("@factoryCode", "100");//工厂代码
						parameters.Add("@enabled", 'Y');//启用
						parameters.Add("@updateUserid", 0);//修改人
						parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//修改日期

						parameters.Add("@ref_work_order_no", mesWmsWork.RefWorkOrderNo);//顶层订单编码
						parameters.Add("@receiving_date", mesWmsWork.ReceivingDate);// 接单日期
						parameters.Add("@order_delivery_date", mesWmsWork.OrderDeliveryDate);//订单交货期
						parameters.Add("@operation_code", mesWmsWork.OperationCode);//工序编码
						parameters.Add("@operation_name", mesWmsWork.OperationName);//工序名称
						parameters.Add("@work_type", mesWmsWork.WorkType);//工种
						parameters.Add("@quota_salary", mesWmsWork.QuotaSalary);//定额工资
						parameters.Add("@mould", mesWmsWork.Mould);//模具
						parameters.Add("@frock", mesWmsWork.Frock);//工装
						parameters.Add("@top_operation_code", mesWmsWork.TopOperationCode);//前工序编码
						parameters.Add("@top_operation_name", mesWmsWork.TopOperationName);//前工序名称
						parameters.Add("@post_operation_code", mesWmsWork.PostOperationCode);//后工序编码
						parameters.Add("@post_operation_name", mesWmsWork.PostOperationName);//后工序名称

						
						parameters.Add("@classes_code", mesWmsWork.ClassesCode);//班次编码：
						parameters.Add("@classes_name", mesWmsWork.ClassesName);//班次名称
						parameters.Add("@latest_end_time", mesWmsWork.LatestEndTime);//最晚结束时间
						parameters.Add("@produce_start_time", mesWmsWork.ProduceStartTime);//生产开始时间
						parameters.Add("@produce_end_time", mesWmsWork.ProduceEndTime);//生产结束时间
						parameters.Add("@produce_time", mesWmsWork.ProduceTime);//生产时间
						parameters.Add("@work_code", mesWmsWork.WorkCode);//工作编码
						parameters.Add("@change_time", mesWmsWork.ChangeTime);//切换时间
						parameters.Add("@is_repair", mesWmsWork.IsRepair);//是否返修
						parameters.Add("@equipment_code", mesWmsWork.EquipmentCode);//设备编码

						parameters.Add("@GUID", mesWorkOrders[0].Guid);//GUID
						parametersList.Add(parameters);
					}
					else
					{
						SqlStringBuilder = new StringBuilder(1024);
						SqlStringBuilder.Append("INSERT INTO dbo.mes_work_order(ref_work_order_no,order_type,order_status,receiving_date,order_delivery_date,order_no,operation_code,operation_name,work_type,quota_salary,mould ");
						SqlStringBuilder.Append("           ,frock,top_operation_code,top_operation_name,post_operation_code,post_operation_name,pdline_name,part_id,part_spec,part_no,part_name,qty,classes_code,classes_name ");
						SqlStringBuilder.Append("           ,latest_end_time,produce_start_time,produce_end_time,produce_time,work_code,change_time,is_repair,equipment_code,pdline_id ,pdline_code ");
						SqlStringBuilder.Append("           ,factory_id,factory_code,enabled,create_userid,create_time) ");
						SqlStringBuilder.Append("     VALUES (@ref_work_order_no,@order_type,@order_status,@receiving_date,@order_delivery_date,@order_no,@operation_code,@operation_name,@work_type,@quota_salary,@mould ");
						SqlStringBuilder.Append("           ,@frock,@top_operation_code,@top_operation_name,@post_operation_code,@post_operation_name,@pdline_name,@part_id,@part_spec,@part_no,@part_name,@qty,@classes_code,@classes_name ");
						SqlStringBuilder.Append("           ,@latest_end_time,@produce_start_time,@produce_end_time,@produce_time,@work_code,@change_time,@is_repair,@equipment_code ,@pdlineId ,@pdlineCode ");
						SqlStringBuilder.Append("           ,@factory_id,@factory_code,@enabled,@create_userid,@create_time) ");


						sqlList.Add(SqlStringBuilder.ToString());
						parameters = new DynamicParameters();

						parameters.Add("@ref_work_order_no", mesWmsWork.RefWorkOrderNo);//顶层订单编码
						parameters.Add("@order_type", mesWmsWork.OrderType);//JIT类型
						parameters.Add("@order_status", "10");//状态 创建
						parameters.Add("@receiving_date", mesWmsWork.ReceivingDate);// 接单日期
						parameters.Add("@order_delivery_date", mesWmsWork.OrderDeliveryDate);//订单交货期
						parameters.Add("@order_no", mesWmsWork.WorkOrderNo);// 生产订单号 
                        parameters.Add("@operation_code", mesWmsWork.OperationCode);//工序编码
						parameters.Add("@operation_name", mesWmsWork.OperationName);//工序名称
						parameters.Add("@work_type", mesWmsWork.WorkType);//工种
						parameters.Add("@quota_salary", mesWmsWork.QuotaSalary);//定额工资
						parameters.Add("@mould", mesWmsWork.Mould);//模具
						parameters.Add("@frock", mesWmsWork.Frock);//工装
						parameters.Add("@top_operation_code", mesWmsWork.TopOperationCode);//前工序编码
						parameters.Add("@top_operation_name", mesWmsWork.TopOperationName);//前工序名称
						parameters.Add("@post_operation_code", mesWmsWork.PostOperationCode);//后工序编码
						parameters.Add("@post_operation_name", mesWmsWork.PostOperationName);//后工序名称

                        if (sysPdlines.Count > 0)////资源编码：工单所在工作中心的编码。
                        {
                            parameters.Add("@pdlineId", sysPdlines[0].PdlineId);//生产线id
                            parameters.Add("@pdlineCode", sysPdlines[0].PdlineCode);//生产线代码
                            parameters.Add("@pdline_name", sysPdlines[0].PdlineName);//生产线名称

                        }
                        else
                        {
                            parameters.Add("@pdlineId", 0);//生产线id
                            parameters.Add("@pdlineCode", mesWmsWork.PdlineCode);//生产线代码
                            parameters.Add("@pdline_name", mesWmsWork.PdlineName);//生产线名称
                        }
                        if (sysParts.Count > 0)
						{
							//,
							parameters.Add("@part_id", sysParts[0].PartId);//零件号ID
							parameters.Add("@part_spec", sysParts[0].PartSpec);//简码
							parameters.Add("@part_name", sysParts[0].PartSpec2);//零件名称
						}
						else
						{
							parameters.Add("@part_id", 0);//零件号ID
							parameters.Add("@part_spec", "");//简码
							parameters.Add("@part_name", mesWmsWork.PartName);//零件名称
						}
						parameters.Add("@part_no", mesWmsWork.PartNo);//零件号

						parameters.Add("@qty", mesWmsWork.Qty);//数量
						parameters.Add("@classes_code", mesWmsWork.ClassesCode);//班次编码：
						parameters.Add("@classes_name", mesWmsWork.ClassesName);//班次名称
						parameters.Add("@latest_end_time", mesWmsWork.LatestEndTime);//最晚结束时间
						parameters.Add("@produce_start_time", mesWmsWork.ProduceStartTime);//生产开始时间
						parameters.Add("@produce_end_time", mesWmsWork.ProduceEndTime);//生产结束时间
						parameters.Add("@produce_time", mesWmsWork.ProduceTime);//生产时间
						parameters.Add("@work_code", mesWmsWork.WorkCode);//生产订单编码
                        parameters.Add("@change_time", mesWmsWork.ChangeTime);//切换时间
						parameters.Add("@is_repair", mesWmsWork.IsRepair);//是否返修
						parameters.Add("@equipment_code", mesWmsWork.EquipmentCode);//设备编码


                        if (sysFactory.Count == 0)
                        {
                            parameters.Add("@factory_id", 0);//工厂ID
                            parameters.Add("@factory_code", "");//工厂代码
                        }
                        else
                        {
                            parameters.Add("@factory_id", sysFactory[0].FactoryId);//工厂ID
                            parameters.Add("@factory_code", sysFactory[0].FactoryCode);//工厂代码
                        }

						parameters.Add("@enabled", 'Y');//启用
						parameters.Add("@create_userid", 0);//创建人
						parameters.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//创建日期
						parametersList.Add(parameters);


					}

					SqlStringBuilder = new StringBuilder(1024);
					SqlStringBuilder.Append("update MES_WMS_WORK_ORDER set syn_flg=@synFlg,syn_time=@synTime,update_userid=@updateUserid,update_time=@updateTime where GUID=@GUID ");//更新同步状态
					sqlList.Add(SqlStringBuilder.ToString());

					parameters = new DynamicParameters();
					parameters.Add("@GUID", mesWmsWork.Guid);//ID
					parameters.Add("@synFlg", "20");//已同步
					parameters.Add("@synTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//同步时间
					parameters.Add("@updateUserid", 0);//创建人
					parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//创建时间
					parametersList.Add(parameters);

					bool staResult = ExecuteTransaction(sqlList, parametersList);
					if (staResult == false)
					{
						sqlList = new List<string>();
						parametersList = new List<DynamicParameters>();
						SqlStringBuilder = new StringBuilder(1024);
						SqlStringBuilder.Append("update MES_WMS_WORK_ORDER set syn_flg=@synFlg,syn_time=@synTime,err_msg=@errMsg,update_userid=@updateUserid,update_time=@updateTime where GUID=@GUID ");//更新同步状态
						sqlList.Add(SqlStringBuilder.ToString());

						parameters = new DynamicParameters();
						parameters.Add("@GUID", mesWmsWork.Guid);//ID
						parameters.Add("@synFlg", "30");//同步异常
						parameters.Add("@synTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//同步时间
						parameters.Add("@errMsg", "同步错误，请检查接口数据！");//错误
						parameters.Add("@updateUserid", 0);//更新人
						parameters.Add("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//更新时间
						parametersList.Add(parameters);
						ExecuteTransaction(sqlList, parametersList);
						return false;
					}
				}
				return true;
			}
		}
		/// <summary>
		/// 根据拉动组类型获取拉动组信息
		/// </summary>
		/// <returns></returns>
		public List<WmsPullingGroup> GetPullingGroupList(int pullType)
        {
            IDbConnection dbConn = dapperDbContext.GetDbConnection();
            //using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            //{
            //dbConn.Open();
            StringBuilder SqlStringBuilder = new StringBuilder(1024);
            SqlStringBuilder.Append("SELECT * FROM [wms_pulling_group] (nolock) WHERE enabled = 'Y'  ");
            SqlStringBuilder.Append(" AND pulling_type = @pullingType ");

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@pullingType", pullType);

            List<WmsPullingGroup> resultList = dbConn.Query<WmsPullingGroup>(SqlStringBuilder.ToString(), parameters).ToList();

            return resultList;
            //}
        }

        /// <summary>
        /// 根据拉动组代码获取待拉动数据--预测JIT
        /// </summary>
        /// <returns></returns>
        public List<WmsMoveDetail> GetPullingPartList(string pullGroupCode, int workOrderType)
        {
            IDbConnection dbConn = dapperDbContext.GetDbConnection();
            //using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            //{
            //dbConn.Open();
            StringBuilder sql = new StringBuilder(1024);
            sql.Append("SELECT a.order_no,a.part_id,a.part_no,a.part_spec,SUM(a.qty * c.pull_qty) AS qty,a.unit, ");
            sql.Append("	a.factory_id,a.factory_code,b.pdline_id,b.pdline_code ");
            sql.Append("FROM   mes_work_order_part a (nolock) ");
            sql.Append("JOIN dbo.mes_work_order b (nolock) ON a.order_no = b.order_no AND a.parent_part_id = b.part_id ");
            sql.Append("JOIN dbo.wms_pulling_group_part c (nolock) ON a.part_id = c.part_id ");
            sql.Append("WHERE a.pull_status = 10 ");
            sql.Append("	AND b.order_type = @orderType ");
            sql.Append("	AND a.enabled = 'Y' ");
            sql.Append("	AND c.enabled = 'Y' ");
            sql.Append("	AND c.group_code = @groupCode ");
            sql.Append("GROUP BY a.order_no,a.part_id,a.part_no,a.part_spec,a.unit, ");
            sql.Append("    a.factory_id,a.factory_code,b.pdline_id,b.pdline_code ");
            sql.Append("ORDER BY a.order_no  ");

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@orderType", workOrderType);
            parameters.Add("@groupCode", pullGroupCode);

            List<WmsMoveDetail> resultList = dbConn.Query<WmsMoveDetail>(sql.ToString(), parameters).ToList();

            return resultList;
            //}
        }

        /// <summary>
        /// 获取待拉动数据--生产JIT数据给预测JIT拉动做补充
        /// </summary>
        /// <returns></returns>
        public List<WmsMoveDetail> GetPullingPartList()
        {
            IDbConnection dbConn = dapperDbContext.GetDbConnection();
            //using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            //{
            //dbConn.Open();
            StringBuilder sql = new StringBuilder(1024);
            sql.Append("SELECT a.order_no,a.part_id,a.part_no,a.part_spec,SUM(a.qty * c.pull_qty) AS qty,a.unit, ");
            sql.Append("	a.factory_id,a.factory_code,b.pdline_id,b.pdline_code ");
            sql.Append("FROM   mes_work_order_part a (nolock) ");
            sql.Append("JOIN dbo.mes_work_order b (nolock) ON a.order_no = b.order_no AND a.parent_part_id = b.part_id ");
            sql.Append("JOIN dbo.wms_pulling_group_part c (nolock) ON a.part_id = c.part_id ");
            sql.Append("JOIN dbo.wms_pulling_group d (nolock) ON c.group_code = d.group_code ");
            sql.Append("WHERE b.pull_status = 10 ");
            sql.Append("	AND b.order_type = @orderType ");
            sql.Append("	AND b.enabled = 'Y' ");
            sql.Append("	AND c.enabled = 'Y' ");
            sql.Append("	AND d.enabled = 'Y' ");
            sql.Append("	AND d.pulling_type = @pullingType ");

            sql.Append("GROUP BY a.order_no,a.part_id,a.part_no,a.part_spec,a.unit, ");
            sql.Append("    a.factory_id,a.factory_code,b.pdline_id,b.pdline_code ");

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@orderType", (int)MesEnumUtil.WorkOrderType.PRODUCT_JIT_PULL);
            parameters.Add("@pullingType", (int)WmsEnumUtil.PullGroupType.EARLY_JIT);

            List<WmsMoveDetail> resultList = dbConn.Query<WmsMoveDetail>(sql.ToString(), parameters).ToList();

            return resultList;
            //}
        }

        /// <summary>
        /// 需要生成安全库存零件的列表
        /// </summary>
        /// <returns></returns>
        public List<SysLocatePart> SafetyPullList()
        {
            IDbConnection dbConn = dapperDbContext.GetDbConnection();
            //using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            //{
            //dbConn.Open();
            StringBuilder sql = new StringBuilder(1024);
            sql.Append(@"SELECT  a.guid ,
                                c.warehouse_id ,
                                c.warehouse_name ,
                                c.zone_id ,
                                c.zone_name ,
                                d.erp_warehouse ,
                                a.locate_id ,
                                c.locate_name ,
                                c.locate_desc ,
                                a.factory_id ,
                                a.factory_code ,
                                a.part_id ,
                                a.part_no ,
                                e.part_spec ,
                                a.max_qty ,
                                a.min_qty ,
                                a.safety_qty ,
                                ISNULL(b.qty, 0) Total 
                                --ISNULL(f.Total2, 0) Total2
                        FROM    sys_locate_part AS a ( NOLOCK )
                                LEFT JOIN ( SELECT  locate_name ,
                                                    part_id ,
                                                    SUM(qty) qty
                                            FROM    sys_stock (NOLOCK)
                                            WHERE   status = @status
                                                    AND enabled = 'Y'
                                            GROUP BY locate_name ,
                                                    part_id
                                          ) AS b ON a.part_id = b.part_id
                                                    AND a.locate_name = b.locate_name
                                RIGHT JOIN sys_locate AS c ( NOLOCK ) ON a.locate_id = c.locate_id
                                LEFT JOIN sys_zone AS d ( NOLOCK ) ON c.zone_id = d.zone_id
                                LEFT JOIN sys_part AS e ( NOLOCK ) ON a.part_no = e.part_no
                                --LEFT JOIN ( SELECT  part_id ,
                                --                    SUM(qty) Total2
                                --            FROM    sys_stock (NOLOCK)
                                --            WHERE   status = 50
                                --                    AND enabled = 'Y'
                                --            GROUP BY part_id
                                --          ) AS f ON a.part_id = f.part_id
                        WHERE   a.safety_qty > ISNULL(b.qty, 0)
                                AND is_safety_pull = '0'
                                AND c.locate_type = @locate_type
                                AND a.enabled = 'Y';");

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@status", (int)WmsEnumUtil.StockStatus.INSTOCKED);
            parameters.Add("@locate_type", (int)WmsEnumUtil_Extend.LocateTypeExtend.SOLIDWAREHOUSE);

            List<SysLocatePart> resultList = dbConn.Query<SysLocatePart>(sql.ToString(), parameters).ToList();

            return resultList;
            //}
        }

        public List<WmsMoveHeader> GetMoveHeaderWrong0()
        {
            IDbConnection dbConn = dapperDbContext.GetDbConnection();
            //using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            //{
            //dbConn.Open();
            StringBuilder sql = new StringBuilder(1024);
            sql.Append(@"  select a.guid,a.order_no,b.part_id,b.part_no,isnull(f.qty,0) qty from wms_move_header as a left join wms_move_detail as b on a.order_no = b.order_no 
  left join  (select part_id, sum(qty) qty from sys_stock (nolock) where status = 50 and enabled = 'Y'   group by part_id) as f on b.part_id = f.part_id  
  where a.wrong_sign='1' ");
             
            List<WmsMoveHeader> resultList = dbConn.Query<WmsMoveHeader>(sql.ToString()).ToList();

            return resultList;
            //}
        }

        /// <summary>
        /// 大件拣选任务零件选择
        /// </summary>
        /// <param name="pullGroupCode"></param>
        /// <param name="workOrderType"></param>
        /// <returns></returns>
        public List<WmsPullingOrderDetail> GetPullingPickPartList(string pullGroupCode, int workOrderType)
        {
            IDbConnection dbConn = dapperDbContext.GetDbConnection();
            //using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            //{
            //dbConn.Open();
            StringBuilder sql = new StringBuilder(1024);
            sql.Append("SELECT a.order_no,a.ruid,b.order_seq work_order_seq,a.part_id,a.part_no,a.part_spec,b.part_id parent_part_id,b.part_no parent_part_no,b.part_spec parent_part_spec,a.unit, a.qty * c.pull_qty qty, ");
            sql.Append("	a.factory_id,a.factory_code,b.pdline_id,b.pdline_code ");
            sql.Append("FROM   mes_work_order_part a (nolock) ");
            sql.Append("JOIN dbo.mes_work_order b (nolock) ON a.order_no = b.order_no AND a.parent_part_id = b.part_id ");
            sql.Append("JOIN dbo.wms_pulling_group_part c (nolock) ON a.part_id = c.part_id ");
            sql.Append("WHERE a.pull_status = 10 ");
            sql.Append("	AND b.order_type = @orderType ");
            sql.Append("	AND a.enabled = 'Y' ");
            sql.Append("	AND c.enabled = 'Y' ");
            sql.Append("	AND c.group_code = @groupCode ");
            //sql.Append("GROUP BY a.order_no,a.part_id,a.part_no,a.part_spec,a.unit, ");
            //sql.Append("    a.factory_id,a.factory_code,b.pdline_id,b.pdline_code ");
            //sql.Append("ORDER BY a.order_no  ");

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@orderType", workOrderType);
            parameters.Add("@groupCode", pullGroupCode);

            List<WmsPullingOrderDetail> resultList = dbConn.Query<WmsPullingOrderDetail>(sql.ToString(), parameters).ToList();

            return resultList;
            //}
        }

        /// <summary>
        /// 根据产线ID获取产线库区关系信息
        /// </summary>
        /// <param name="pdlineId"></param>
        /// <returns></returns>
        public SysPdlineZone GetPdlineZone(int pdlineId)
        {
            IDbConnection dbConn = dapperDbContext.GetDbConnection();
            //using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            //{
            //dbConn.Open();
            StringBuilder SqlStringBuilder = new StringBuilder(1024);
            SqlStringBuilder.Append("SELECT top 1 *,z.erp_warehouse FROM [dbo].[sys_pdline_zone] pz (nolock) ");
            SqlStringBuilder.Append("JOIN dbo.sys_zone z (nolock) ON pz.zone_id = z.zone_id ");

            SqlStringBuilder.Append("WHERE pz.pdline_id = @pdlineId ");
            SqlStringBuilder.Append("      AND pz.busi_type = @busiType ");
            SqlStringBuilder.Append("      AND pz.enabled = 'Y' ");

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@pdlineId", pdlineId);
            parameters.Add("@busiType", (int)WmsEnumUtil.PdlineZoneBusiType.BIG_PART_PULL);

            var result = dbConn.QueryFirstOrDefault<SysPdlineZone>(SqlStringBuilder.ToString(), parameters);

            return result;
            //}
        }

        /// <summary>
        /// 根据订单号更新订单拉动状态
        /// </summary>
        /// <returns></returns>
        public int UpdateWorkOrder(string orderNo)
        {
            IDbConnection dbConn = dapperDbContext.GetDbConnection();
            //using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            //{
            //dbConn.Open();
            StringBuilder sql = new StringBuilder(1024);
            sql.Append("UPDATE dbo.mes_work_order SET pull_status = @pullStatus WHERE order_no = @orderNo ");

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@pullStatus", (int)MesEnumUtil.WorkOrderPullStatus.PULLED);
            parameters.Add("@orderNo", orderNo);

            return dbConn.Execute(sql.ToString(), parameters);
            //}
        }

        /// <summary>
        /// 获取待拉动JIT订单数据
        /// </summary>
        /// <returns></returns>
        public List<MesWorkOrder> GetPullingJitOrderList()
        {
            IDbConnection dbConn = dapperDbContext.GetDbConnection();
            //using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            //{
            //dbConn.Open();
            StringBuilder sql = new StringBuilder(1024);
            sql.Append("SELECT TOP 1000 * FROM mes_work_order (nolock) ");
            sql.Append("WHERE order_type = @orderType ");
            sql.Append("ORDER BY order_seq ");

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@orderType", (int)MesEnumUtil.WorkOrderType.PRODUCT_JIT);

            List<MesWorkOrder> resultList = dbConn.Query<MesWorkOrder>(sql.ToString(), parameters).ToList();
            return resultList;
            //}
        }

        /// <summary>
        /// 获取待拉动JIT订单原材料数据
        /// </summary>
        /// <returns></returns>
        public List<MesWorkOrderPart> GetPullingJitOrderPartList()
        {
            IDbConnection dbConn = dapperDbContext.GetDbConnection();
            //using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            //{
            //dbConn.Open();
            StringBuilder sql = new StringBuilder(1024);
            sql.Append("SELECT TOP 1000 * FROM mes_work_order (nolock) ");
            sql.Append("WHERE order_type = @orderType ");
            sql.Append("ORDER BY order_seq ");

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@orderType", (int)MesEnumUtil.WorkOrderType.PRODUCT_JIT);

            List<MesWorkOrderPart> resultList = dbConn.Query<MesWorkOrderPart>(sql.ToString(), parameters).ToList();
            return resultList;
            //}
        }
    }
}
